package com.demoweb.model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.demoweb.model.dto.Board;
import com.demoweb.model.dto.CandyItem;


public class CandyItemDao {
	

public int insertitem(CandyItem  item ) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int insertedId = -1;
		
		try {
			conn = ConnectionHelper.getConnection();
			
			//conn.setAutoCommit(false);
			
			StringBuilder sql = new StringBuilder(256);
			sql.append("INSERT INTO candyitem  ")
			   .append("(itemname, content, popularityCount,itemFileNo, itemFileName) ")
			   .append("VALUES ")
			   .append("(?, ?, ?, ?, ? )");
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setString(1, item.getItemTitle());
			pstmt.setString(2, item.getContent());
			pstmt.setInt(3, item.getPopularityCount());
			pstmt.setInt(4, item.getItemFileNo());
			pstmt.setString(5, item.getItemFileName());
			
			
			pstmt.executeUpdate();
			pstmt.close();
			
			//sql.delete(0, sql.length());
			//LAST_INSERT_ID() : 현재 세션에서 마지막으로 처리된 자동 증가 값
//			sql.append("UPDATE item ")
//			   .append("SET groupno = LAST_INSERT_ID() ")
//			   .append("WHERE itemno = LAST_INSERT_ID()");
//			pstmt = conn.prepareStatement(sql.toString());
			
			//conn.commit();
			
		} catch (Exception ex) {
			//try { conn.rollback(); } catch (Exception ex2) {}
			ex.printStackTrace();
		} finally {
			//6. 연결닫기
			try { pstmt.close(); } catch (Exception ex) {}
			try { conn.setAutoCommit(true); } catch (Exception ex) {}
			try { conn.close(); } catch (Exception ex) {}
		}
		
		return insertedId;
	}
	

public List<CandyItem> getItemList() {
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;//조회 결과에 접근하는 참조 변수
	//데이터베이스의 데이터를 읽어서 저장할 객체 컬렉션
	ArrayList<CandyItem> items = new ArrayList<CandyItem>();
	
	try {
		conn = ConnectionHelper.getConnection();
		StringBuilder sql = new StringBuilder(512);
		sql.append("SELECT itemNo, itemname, content, deleted, popularityCount,countchack, itemFileNo, itemFileName  ")
		   .append("FROM candyitem  ")
		   .append("WHERE deleted = false or deleted is null ")
		   .append("ORDER BY popularityCount DESC ");
////		   .append("LIMIT ?, ?");
		pstmt = conn.prepareStatement(sql.toString());
		rs = pstmt.executeQuery();
		
		while (rs.next()) {
			CandyItem item = new CandyItem();
			item.setItemNo(rs.getInt(1));
			item.setItemTitle(rs.getString(2));
			item.setContent(rs.getString(3));
			item.setDeleted(rs.getBoolean(4));
			item.setPopularityCount(rs.getInt(5));
			item.setCountCheck(rs.getBoolean(6));
			item.setItemFileNo(rs.getInt(7));
			item.setItemFileName(rs.getString(8));
			
			items.add(item);
		}
	} catch (Exception ex) {
		ex.printStackTrace();
	} finally {
		//6. 연결닫기
		if (rs != null) try { rs.close(); } catch (Exception ex) {}
		if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
		if (conn != null) try { conn.close(); } catch (Exception ex) {}
	}
	
	return items;
}


public void deleteitem(int itemNo) {
	Connection conn = null;
	PreparedStatement pstmt = null;
	
	try {
		conn = ConnectionHelper.getConnection();
		
		String sql = 
			"UPDATE candyitem  SET deleted = true WHERE itemno = ? ";
		pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, itemNo);
		
		pstmt.executeUpdate();
		
	} catch (Exception ex) {
		ex.printStackTrace();
	} finally {
		//6. 연결닫기
		if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
		if (conn != null) try { conn.close(); } catch (Exception ex) {}
	}
}



public void increasecountitem(int itemNo) {
	Connection conn = null;
	PreparedStatement pstmt = null;
	
	try {
		conn = ConnectionHelper.getConnection();
		
		String sql = 
		"UPDATE candyitem  SET popularityCount = popularityCount +1  WHERE itemno = ? ";
		pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, itemNo);
		
		pstmt.executeUpdate();
		
	} catch (Exception ex) {
		ex.printStackTrace();
	} finally {
		//6. 연결닫기
		if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
		if (conn != null) try { conn.close(); } catch (Exception ex) {}
	}
}




public void inserttwit(Board board) {
	
	Connection conn = null;
	PreparedStatement pstmt = null;
	
	
	try {
		conn = ConnectionHelper.getConnection();
		
		conn.setAutoCommit(false);
		
		StringBuilder sql = new StringBuilder(256);
		sql.append("INSERT INTO board ")
		   .append("(title, writer, content, groupno, step) ")
		   .append("VALUES ")
		   .append("(?, ?, ?, -1, 1)");
		pstmt = conn.prepareStatement(sql.toString());
		pstmt.setString(1, board.getTitle());
		pstmt.setString(2, board.getWriter());
		pstmt.setString(3, board.getContent());
		
		pstmt.executeUpdate();
		pstmt.close();
		
		sql.delete(0, sql.length());
		//LAST_INSERT_ID() : 현재 세션에서 마지막으로 처리된 자동 증가 값
		sql.append("UPDATE board ")
		   .append("SET groupno = LAST_INSERT_ID() ")
		   .append("WHERE boardno = LAST_INSERT_ID()");
		pstmt = conn.prepareStatement(sql.toString());
		
		pstmt.executeUpdate();
		
		conn.commit();
		
	} catch (Exception ex) {
		try { conn.rollback(); } catch (Exception ex2) {}
		ex.printStackTrace();
	} finally {
		//6. 연결닫기
		try { pstmt.close(); } catch (Exception ex) {}
		try { conn.setAutoCommit(true); } catch (Exception ex) {}
		try { conn.close(); } catch (Exception ex) {}
	}
}


}











